﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Transactions;

namespace IntellectShop.Common
{
	public partial class Course : Commodity
	{
		public static List<Common.Category> RetrieveCategoriesHavingCourses()
		{
			using (DB.IntellectShopDataContext context = new DB.IntellectShopDataContext(Globals.ConnectionString))
			{
				var query =
							from category in context.Categories
							join course in context.Courses
								on category.CategoryID equals course.CategoryID
							group category by new
							{
								category.CategoryID,
								category.SortingID,
								category.Name
							} into g
							orderby g.Key.SortingID descending, g.Key.Name
							select g.Key;

				List<Common.Category> result = new List<Category>();
				foreach (var row in query)
				{
					result.Add(new Common.Category
					{
						CategoryID = row.CategoryID,
						Name = row.Name,
						SortingID = row.SortingID
					});
				}

				return result;
			}
		}

		public static Course RetrieveByCourseID(int courseID)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var query = (
							from course in dataContext.Courses
							where course.CourseID == courseID
							join commodity in dataContext.Commodities
								on course.CommodityID equals commodity.CommodityID
							join category in dataContext.Categories
								on course.CategoryID equals category.CategoryID
							orderby commodity.SortingID descending, commodity.Code
							select new
							{
								category.CategoryID,
								CategoryName = category.Name,
								course.DurationHours,
								course.DurationDays,
								course.CourseID,
								course.CourseProgramm,
								commodity.CommodityID,
								commodity.SortingID,
								commodity.Code,
								commodity.Name,
								commodity.Price,
								commodity.Descr
							}).SingleOrDefault();

				if (query == null)
					return null;

				Common.Category selectedCategory = new Common.Category { CategoryID = query.CategoryID, Name = query.CategoryName };

				Common.Course selectedCourse = new Common.Course
				{
					CourseID = query.CourseID,
					Category = selectedCategory,
					Code = query.Code,
					CommodityID = query.CommodityID,
					SortingID = query.SortingID,
					Description = query.Descr,
					Duration = new Common.Duration(query.DurationHours, query.DurationDays),
					Name = query.Name,
					Price = new Common.Money { Amount = (query.Price.HasValue ? query.Price.Value : 0), Currency = Currency.RUB },
					CourseProgramm = query.CourseProgramm,
					Schedule = Common.Schedule.RetrieveByCourseID(query.CourseID)
				};

				return selectedCourse;
			}
		}

		public static Course RetrieveByCommodityID(int commodityID)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var query = (
							from commodity in dataContext.Commodities
							where commodity.CommodityID == commodityID
							join course in dataContext.Courses
								on commodity.CommodityID equals course.CommodityID
							join category in dataContext.Categories
								on course.CategoryID equals category.CategoryID
							select new
							{
								category.CategoryID,
								CategoryName = category.Name,
								CategorySortingID = category.SortingID,
								course.DurationHours,
								course.DurationDays,
								course.CourseID,
								course.CourseProgramm,
								commodity.CommodityID,
								commodity.SortingID,
								commodity.Code,
								commodity.Name,
								commodity.Price,
								commodity.Descr
							}).SingleOrDefault();

				if (query == null)
					return null;

				Common.Category selectedCategory = new Common.Category { CategoryID = query.CategoryID, Name = query.CategoryName, SortingID = query.CategorySortingID };

				Common.Course selectedCourse = new Common.Course
				{
					CourseID = query.CourseID,
					Category = selectedCategory,
					Code = query.Code,
					CommodityID = query.CommodityID,
					SortingID = query.SortingID,
					Description = query.Descr,
					Duration = new Common.Duration(query.DurationHours, query.DurationDays),
					Name = query.Name,
					Price = new Common.Money { Amount = (query.Price.HasValue ? query.Price.Value : 0), Currency = Currency.RUB },
					CourseProgramm = query.CourseProgramm,
					Schedule = Common.Schedule.RetrieveByCourseID(query.CourseID)
				};

				return selectedCourse;
			}
		}

		public static Dictionary<Common.Category, List<Common.Course>> RetrieveCoursesByCategory()
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var query = (
							from commodity in dataContext.Commodities
							join course in dataContext.Courses
								on commodity.CommodityID equals course.CommodityID
							join category in dataContext.Categories
								on course.CategoryID equals category.CategoryID
							orderby category.SortingID descending, commodity.SortingID descending, commodity.Code
							select new
							{
								category.CategoryID,
								CategoryName = category.Name,
								CategorySortingID = category.SortingID,
								course.DurationHours,
								course.DurationDays,
								course.CourseID,
								course.CourseProgramm,
								course.Created,
								course.CreatedBy,
								course.Updated,
								course.UpdatedBy,
								commodity.CommodityID,
								CommoditySortingID = commodity.SortingID,
								commodity.SortingID,
								commodity.Code,
								commodity.Name,
								commodity.Price,
								commodity.Descr
							});

				Dictionary<Common.Category, List<Common.Course>> result = new Dictionary<Common.Category, List<Common.Course>>();
				Common.Category selectedCategory = null;
				foreach (var row in query)
				{
					selectedCategory = new Common.Category { CategoryID = row.CategoryID, Name = row.CategoryName, SortingID = row.CategorySortingID };
					List<Common.Course> courses;
					if (result.TryGetValue(selectedCategory, out courses))
					{
						if (courses == null)
						{
							courses = new List<Course>();
						}
					}
					else
					{
						courses = new List<Course>();
						result.Add(selectedCategory, courses);
					}

					courses.Add(new Common.Course
					{
						CourseID = row.CourseID,
						Category = selectedCategory,
						Code = row.Code,
						CommodityID = row.CommodityID,
						SortingID = row.CommoditySortingID,
						Duration = new Common.Duration(row.DurationHours, row.DurationDays),
						CourseProgramm = row.CourseProgramm,
						Description = row.Descr,
						Name = row.Name,
						Price = new Common.Money { Amount = (row.Price.HasValue ? row.Price.Value : 0), Currency = Currency.RUB },
						Schedule = Common.Schedule.RetrieveByCourseID(row.CourseID),
						Created = row.Created,
						CreatedBy = row.CreatedBy,
						Updated = row.Updated,
						UpdatedBy = row.UpdatedBy
					});
				}

				return result;
			}
		}

		public static Dictionary<Common.Category, List<Common.Course>> RetrieveCoursesByCategory(DateTime periodBegin, DateTime periodEnd)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var query =
							from commodity in dataContext.Commodities
							join course in dataContext.Courses
								on commodity.CommodityID equals course.CommodityID
							join category in dataContext.Categories
								on course.CategoryID equals category.CategoryID
							join scheduleItems in dataContext.ScheduleItems
								on course.CourseID equals scheduleItems.CourseID
							let start = (DateTime)scheduleItems.EventDate.Date
							let end = (DateTime)scheduleItems.EventDate.Date
							where start >= periodBegin && end < periodEnd
							group course by new
							{
								category.CategoryID,
								category.SortingID,
								CategoryName = category.Name,
								CategorySortingID = category.SortingID,
								course.DurationHours,
								course.DurationDays,
								course.CourseID,
								course.Created,
								course.CreatedBy,
								course.Updated,
								course.UpdatedBy,
								commodity.CommodityID,
								CommoditySortingID = commodity.SortingID,
								commodity.Code,
								commodity.Name,
								commodity.Price
							} into g
							orderby g.Key.SortingID descending, g.Key.CommoditySortingID descending, g.Key.Code
							select g.Key;

				Dictionary<Common.Category, List<Common.Course>> result = new Dictionary<Common.Category, List<Common.Course>>();
				Common.Category selectedCategory = null;
				foreach (var row in query)
				{
					selectedCategory = new Common.Category { CategoryID = row.CategoryID, Name = row.CategoryName, SortingID = row.CategorySortingID };
					List<Common.Course> courses;
					if (result.TryGetValue(selectedCategory, out courses))
					{
						if (courses == null)
						{
							courses = new List<Course>();
						}
					}
					else
					{
						courses = new List<Course>();
						result.Add(selectedCategory, courses);
					}

					courses.Add(new Common.Course
					{
						CourseID = row.CourseID,
						Category = selectedCategory,
						Code = row.Code,
						CommodityID = row.CommodityID,
						SortingID = row.CommoditySortingID,
						Duration = new Common.Duration(row.DurationHours, row.DurationDays),
						Name = row.Name,
						Price = new Common.Money { Amount = (row.Price.HasValue ? row.Price.Value : 0), Currency = Currency.RUB },
						Schedule = Common.Schedule.RetrieveByCourseIDAndPeriod(row.CourseID, periodBegin),
						Created = row.Created,
						CreatedBy = row.CreatedBy,
						Updated = row.Updated,
						UpdatedBy = row.UpdatedBy
					});
				}

				return result;
			}
		}

		public static List<Common.Course> RetrieveCoursesByCategoryID(int categoryID)
		{
			List<Common.Course> selectedCourses = new List<Common.Course>();
			Common.Category selectedCategory = Common.Category.RetrieveByID(categoryID);
			if (selectedCategory == null)
			{
				return selectedCourses;
			}

			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var queryCourses =
							from category in dataContext.Categories
							where category.CategoryID == categoryID
							join course in dataContext.Courses
								on category.CategoryID equals course.CategoryID
							join commodity in dataContext.Commodities
								on course.CommodityID equals commodity.CommodityID
							orderby commodity.SortingID descending, commodity.Code
							select new
							{
								category.CategoryID,
								CategoryName = category.Name,
								course.DurationHours,
								course.DurationDays,
								course.CourseID,
								course.CourseProgramm,
								commodity.CommodityID,
								commodity.SortingID,
								commodity.Code,
								commodity.Name,
								commodity.Price,
								commodity.Descr
							};

				foreach (var each in queryCourses)
				{
					selectedCourses.Add(new Common.Course
					{
						CourseID = each.CourseID,
						Category = selectedCategory,
						Code = each.Code,
						CommodityID = each.CommodityID,
						SortingID = each.SortingID,
						Description = each.Descr,
						Duration = new Common.Duration(each.DurationHours, each.DurationDays),
						Name = each.Name,
						Price = new Common.Money { Amount = (each.Price.HasValue ? each.Price.Value : 0), Currency = Currency.RUB },
						CourseProgramm = each.CourseProgramm
					});
				}

				return selectedCourses;
			}
		}

		public static List<Common.Course> RetrieveNearestCourses(int count)
		{
			List<Common.Course> selectedCourses = new List<Common.Course>();

			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var queryCourses = (
									from commodity in dataContext.Commodities
									join course in dataContext.Courses
										on commodity.CommodityID equals course.CommodityID
									join category in dataContext.Categories
										on course.CategoryID equals category.CategoryID
									join scheduleItems in dataContext.ScheduleItems
										on course.CourseID equals scheduleItems.CourseID
									where scheduleItems.EventDate.Date >= DateTime.Now.Date
									orderby scheduleItems.EventDate, commodity.SortingID descending, commodity.Code
									select new
									{
										category.CategoryID,
										CategoryName = category.Name,
										course.DurationHours,
										course.DurationDays,
										course.CourseID,
										course.CourseProgramm,
										commodity.CommodityID,
										commodity.SortingID,
										commodity.Code,
										commodity.Name,
										commodity.Price,
										commodity.Descr
									}).Take(count);

				foreach (var each in queryCourses)
				{
					selectedCourses.Add(new Common.Course
					{
						CourseID = each.CourseID,
						Category = new Category { CategoryID = each.CategoryID, Name = each.CategoryName },
						Code = each.Code,
						CommodityID = each.CommodityID,
						SortingID = each.SortingID,
						Description = each.Descr,
						Duration = new Common.Duration(each.DurationHours, each.DurationDays),
						Name = each.Name,
						Price = new Common.Money { Amount = (each.Price.HasValue ? each.Price.Value : 0), Currency = Currency.RUB },
						CourseProgramm = each.CourseProgramm
					});
				}

				return selectedCourses;
			}
		}

		public new void Create(string createdBy)
		{
			using (TransactionScope trans = new TransactionScope())
			{
				using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
				{
					var commodityDB = new DB.Commodity
						{
							Code = this.Code,
							Descr = this.Description,
							Name = this.Name,
							Price = this.Price.Amount,
							SortingID = this.SortingID,
							Created = DateTime.Now,
							CreatedBy = createdBy,
							CommodityType = (int)this.CommodityType
						};
					dataContext.Commodities.InsertOnSubmit(commodityDB);
					dataContext.SubmitChanges();

					var courseDB = new DB.Course
						{
							CommodityID = commodityDB.CommodityID,
							CategoryID = this.Category.CategoryID,
							DurationHours = this.Duration.Hours,
							DurationDays = this.Duration.Days,
							CourseProgramm = this.CourseProgramm,
							Created = DateTime.Now,
							CreatedBy = createdBy
						};
					dataContext.Courses.InsertOnSubmit(courseDB);
					dataContext.SubmitChanges();
					this.CourseID = courseDB.CourseID;
					this.CommodityID = commodityDB.CommodityID;

					trans.Complete();
				}
			}

			this.Tags.CommodityID = this.CommodityID;
			this.Tags.Save();
		}

		public new void Update(string updateBy)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var courseDB = dataContext.Courses.SingleOrDefault(x => x.CommodityID == this.CommodityID);
				courseDB.CategoryID = this.Category.CategoryID;
				courseDB.DurationHours = this.Duration.Hours;
				courseDB.DurationDays = this.Duration.Days;
				courseDB.CourseProgramm = this.CourseProgramm;
				courseDB.Updated = DateTime.Now;
				courseDB.UpdatedBy = updateBy;

				courseDB.Commodity.SortingID = this.SortingID;
				courseDB.Commodity.Name = this.Name;
				courseDB.Commodity.Code = this.Code;
				courseDB.Commodity.Descr = this.Description;
				courseDB.Commodity.Price = this.Price.Amount;
				courseDB.Commodity.Updated = DateTime.Now;
				courseDB.Commodity.UpdatedBy = updateBy;

				dataContext.SubmitChanges();
			}

			this.Tags.Save();
		}

		public new static void Delete(int courseID)
		{
			int commodityID;
			using (TransactionScope trans = new TransactionScope())
			{
				using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
				{
					var courseDB = (from course in dataContext.Courses where course.CourseID == courseID select course).SingleOrDefault();
					commodityID = courseDB.CommodityID;

					var scheduleItemsDB = from schedule in dataContext.ScheduleItems where schedule.CourseID == courseID select schedule;
					dataContext.ScheduleItems.DeleteAllOnSubmit(scheduleItemsDB);
					dataContext.SubmitChanges();

					var cartItemsDB = from cart in dataContext.CartItems where cart.CommodityID == commodityID select cart;
					dataContext.CartItems.DeleteAllOnSubmit(cartItemsDB);
					dataContext.SubmitChanges();

					var tagsSetDB = from tags in dataContext.TagsSets where tags.CommodityID == commodityID select tags;
					dataContext.TagsSets.DeleteAllOnSubmit(tagsSetDB);
					dataContext.SubmitChanges();

					dataContext.Courses.DeleteOnSubmit(courseDB);
					dataContext.SubmitChanges();

					var commodityDB = (from commodity in dataContext.Commodities where commodity.CommodityID == commodityID select commodity).SingleOrDefault();
					dataContext.Commodities.DeleteOnSubmit(commodityDB);
					dataContext.SubmitChanges();

					trans.Complete();
				}
			}
		}
	}
}
